from sqlalchemy import Column, Integer, String, text
from sqlalchemy.exc import PendingRollbackError
from sqlalchemy.ext.declarative import declarative_base
from inits import dbSession

# 定义基类
Base = declarative_base()

class shop_report(Base):
    __tablename__ = 'shop_report'  # 数据库中表的名字
    id = Column(Integer, primary_key=True)
    platform = Column(Integer)
    shop_id = Column(Integer)
    avg_response_duration = Column(String(50))
    three_min_response_rate = Column(String(50))
    satisfaction_rate = Column(String(50))
    inquiry_conversion_rate = Column(String(50))
    insert_timestamp = Column(Integer)

    # 删除历史数据
    def delete_data(self, platform, start_time, end_time):
        try:
            sql = text("""
                    DELETE FROM shop_report WHERE platform= :platform and insert_timestamp >= :startTime and insert_timestamp <= :endTime
                """)
            dbSession.execute(sql, {"platform": platform, "startTime": start_time, "endTime": end_time})
            dbSession.commit()
        except PendingRollbackError:
            dbSession.rollback()
        finally:
            dbSession.close()

    def savaShopReport(self, shop_report):

        dbSession.add(shop_report)

        dbSession.flush()  # 将会话的改动发送到数据库

        id = shop_report.id

        # 提交到数据库
        dbSession.commit()

        dbSession.close()
        return id

        # try:
        #     self.session.add(shop_report)
        #
        #     self.session.flush()  # 将会话的改动发送到数据库
        #
        #     id = shop_report.id
        #
        #     # 提交到数据库
        #     self.session.commit()
        #     return id
        # except PendingRollbackError:
        #     self.session.rollback()
        # finally:
        #     # 关闭Session
        #     self.session.close()